<?
include("../functions/functions.php");                                                                                        

$reportTitle = "Clients by number of visits.";
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title><? echo $reportTitle; ?></title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="reports.css" rel="stylesheet" type="text/css">
</head>

<body bgcolor="#FFFFFF" text="#000000" link="#0075C5" vlink="#0075C5" alink="#C5DDF4" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<?

echo "<p style=\"text-align:center; font-size: 16pt\">$reportTitle</h3>";
echo "<p align=\"right\" width=\"50%\">".getCurrentDate()."</p>";
echo "<p>This report displays all clients with more than 4 visits against them within the last 12 months by visits and name.</p>";

echo "<table width=\"100%\" cellpadding=\"3\" cellspacing=\"0\" class=\"body\" style=\"border: 1px solid #000000;\">";
?>

<tr>
	<td class="bodybold" style="vertical-align: top; border: 1px solid #000000;">
		Surname
	</td>
	<td nowrap class="bodybold" style="border: 1px solid #000000;">
		Given Name
	</td>
	<td class="bodybold" style="border: 1px solid #000000;">
		ID Number
	</td>
	<td class="bodybold" style="border: 1px solid #000000;">
		ID Type
	</td>
	<td class="bodybold" style="border: 1px solid #000000;">
		Visits
	</td>
	<td class="bodybold" style="border: 1px solid #000000;">
		Date of visits
	</td>
</tr>

<?
$sql = "SELECT er_client.client_id, er_client.givenname, er_client.surname, er_client.id_reference_no, er_identification_type.identification_type, count(er_visit.visit_id) as numberOfVisits
				FROM `er_client` INNER JOIN er_identification_type ON er_identification_type.identification_no = er_client.identification_no INNER JOIN er_visit ON er_visit.client_id = er_client.client_id AND er_visit.visit_date >= NOW() - INTERVAL 1 YEAR
				GROUP BY er_client.givenname, er_client.surname, er_client.id_reference_no, er_identification_type.identification_type
				ORDER BY numberOfVisits desc, er_client.surname, er_client.givenname";
//echo $sql;

$results = r_db($sql);

while ($row = mysql_fetch_array($results)) {
	$tempgivenname = ucfirst(strtolower($row["givenname"]));
	$tempsurname = ucfirst(strtolower($row["surname"]));
	$tempid_reference_no = $row["id_reference_no"];
	$tempidentification_type = $row["identification_type"];
	$tempnumberOfVisits = $row["numberofvisits"];
	$tempclient_id = $row["client_id"];
	if ($tempnumberOfVisits > 4) {
?>
<tr>
	<td style="vertical-align: top; text-transform: capitalize; border: 1px solid #000000;">
		<? echo $tempsurname; ?>&nbsp;
	</td>
	<td style="vertical-align: top; text-transform: capitalize; border: 1px solid #000000;">
		<? echo $tempgivenname; ?>&nbsp;
	</td>
	<td style="vertical-align: top; border: 1px solid #000000;">
		<? echo $tempid_reference_no; ?>&nbsp;
	</td>
	<td nowrap style="vertical-align: top; border: 1px solid #000000;">
		<? echo $tempidentification_type; ?>&nbsp;
	</td>
	<td style="vertical-align: top; border: 1px solid #000000;">
		<? echo $tempnumberOfVisits; ?>&nbsp;
	</td>
	<td style="vertical-align: top; border: 1px solid #000000;">
		<?
			$sql = "select er_visit.visit_date from er_visit where client_id = $tempclient_id AND er_visit.visit_date >= NOW() - INTERVAL 1 YEAR order by er_visit.visit_date";
			//echo $sql;
			$results_date = r_db($sql);
			$count = 0;

			while ($row = mysql_fetch_array($results_date)) {
				if ($count > 0) {
					echo ", ";
				}
				echo date("d-m-Y",strtotime($row['visit_date']));
				$count++;
			}
		?>&nbsp;
	</td>
</tr>
<?
	}
}
?>
</table>
</body>
</html>